﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using Cocbook_DTO;
using Cocbook_DAL;
using System.IO;
using System.Diagnostics;


namespace Sale_and_manage
{
    public partial class frmImportBookInfo : DevComponents.DotNetBar.Metro.MetroForm
    {
        public frmImportBookInfo()
        {
            InitializeComponent();
            LoadAuthor();
            LoadGenre();
            LoadIssuer();
            LoadPublisher();
        }

        public BookDAL bookDAL = new BookDAL();
        public List<DataGridViewRow> bookErrorList = new List<DataGridViewRow>();
        public List<string> err = new List<string>();

        public DataTable ReadExcelContents(string fileName)
        {
            try
            {
                OleDbConnection connection = new OleDbConnection();

                connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fileName); //Excel 97-2003, .xls
                string excelQuery = @"Select [Mã Sách],	[Tên Sách],	[Đơn vị tính], [Nhà xuất bản], [Công ty phát hành], [Khổ], [Số Trang], 
                                            [Giá Bìa],	[Tủ Sách], [Khối lượng], [Tác Giả]
                                      FROM [Sheet1$]";
                connection.Open();
                OleDbCommand cmd = new OleDbCommand(excelQuery, connection);
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                DataTable dt = ds.Tables[0];
                lblQuantity.Text = dt.Rows.Count.ToString();
                dgvBook.DataSource = dt;
                connection.Close();
                connection.Dispose();
                cmd.Dispose();
                adapter.Dispose();
                ds.Dispose();
                dt.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Program can't read file. " + ex.Message, "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }


        private void rbtFileImport_CheckedChanged(object sender, EventArgs e)
        {
            if (rbtFileImport.Checked)
            {
                groupPanel1.Enabled = true;
                groupPanel2.Enabled = false;
            }
        }

        private void rbtManualImport_CheckedChanged(object sender, EventArgs e)
        {

            if (rbtManualImport.Checked)
            {
                groupPanel2.Enabled = true;
                groupPanel1.Enabled = false;
            }
        }

        public void ImportFromFileExcel(string d)
        {
        }

        private void btnOpenfile_Click(object sender, EventArgs e)
        {
            openFileDialog1.Title = "Open";
            openFileDialog1.FileName = "";
            openFileDialog1.Filter = "Excel 93-2003 Workbook|*.xls";
            if (openFileDialog1.ShowDialog() != DialogResult.Cancel)
            {
                ReadExcelContents(openFileDialog1.FileName);
                if (dgvBook.RowCount > 0)
                {
                    btnStart.Enabled = true;
                }
            }
        }

        public Book GetBookInfo(int row)
        {
            Book b = new Book();
            try
            {
                try
                {
                    b.ISBN = Convert.ToInt64(dgvBook[0, row].Value);
                    if (bookDAL.GetBookByISBN(b.ISBN) != null)
                    {
                        "".Substring(2);
                    }
                }
                catch
                {
                    err.Add("Lỗi ISBN");
                    "".Substring(2);
                }
                if (dgvBook[1, row].Value.ToString() == "")
                {
                    err.Add("Tên sách");
                    "".Substring(2);
                }
                else
                {
                    b.Title = Convert.ToString(dgvBook[1, row].Value);
                }

                if (dgvBook[2, row].Value.ToString() == "")
                {
                    err.Add("ĐVT");
                    "".Substring(2);
                }
                else
                {
                    b.Type = Convert.ToString(dgvBook[2, row].Value);
                }

                if (dgvBook[3, row].Value.ToString() == "")
                {
                    err.Add("NXB");
                    "".Substring(2);
                }
                else
                {
                    b.Publisher = Convert.ToString(dgvBook[3, row].Value);
                }

                if (dgvBook[4, row].Value.ToString() == "")
                {
                    err.Add("Nhà phát hành");
                    "".Substring(2);
                }
                else
                {
                    b.Issuer = Convert.ToString(dgvBook[4, row].Value);
                }
                b.Size = Convert.ToString(dgvBook[5, row].Value);
                b.Page = Convert.ToString(dgvBook[6, row].Value);

                if (dgvBook[7, row].Value.ToString() == "")
                {
                    err.Add("Giá");
                    "".Substring(2);
                }
                else
                {
                    b.Price = Convert.ToDouble(dgvBook[7, row].Value);
                }
                if (dgvBook[8, row].Value.ToString() == "")
                {
                    b.Genre = Convert.ToString(dgvBook[8, row].Value);
                }
                else
                {
                    b.Genre = "";
                }
                if (dgvBook[9, row].Value.ToString() != "")
                {
                    b.Weigh = 0;
                }
                return b;
            }
            catch
            {
                return null;                
            }
        }

        public void ExportErrorBookList()
        {
            string fileLocation = openFileDialog1.FileName;
            fileLocation = fileLocation.Insert(fileLocation.LastIndexOf("."), "-null");
            OleDbConnection connection = new OleDbConnection();
            File.Copy(@"Du lieu sach-null.xls", fileLocation, true);

            connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=Excel 8.0;Data Source=" + fileLocation); 
            OleDbCommand cmd;
            string excelQuery;
            connection.Open();
            for (int i = 0; i < bookErrorList.Count; i++)
            {
                excelQuery = @"INSERT INTO [Sheet1$]([Mã Sách], [Tên Sách], [Đơn vị tính], [Nhà xuất bản], [Công ty phát hành], [Khổ],"+
                                " [Số Trang], [Giá Bìa], [Tủ Sách], [Khối lượng], [Tác Giả], [Lỗi]) "+
                                "VALUES (@isbn, @title, @type, @publisher, @issuer, @size, @page, @price, @genre, @weigh, @author, @err)";
                cmd = new OleDbCommand(excelQuery, connection);                                                                                                                                                                  
                cmd.Parameters.AddWithValue("isbn", bookErrorList[i].Cells[0].Value.ToString());                                                                                                                                      
                cmd.Parameters.AddWithValue("title", bookErrorList[i].Cells[1].Value.ToString());                                                                                                                                     
                cmd.Parameters.AddWithValue("type", bookErrorList[i].Cells[2].Value.ToString());                                                                                                                                      
                cmd.Parameters.AddWithValue("publisher", bookErrorList[i].Cells[3].Value.ToString());                                                                                                                          
                cmd.Parameters.AddWithValue("issuer", bookErrorList[i].Cells[4].Value.ToString());                                                                                                                                    
                cmd.Parameters.AddWithValue("size", bookErrorList[i].Cells[5].Value.ToString());
                cmd.Parameters.AddWithValue("page", bookErrorList[i].Cells[6].Value.ToString());
                cmd.Parameters.AddWithValue("price", bookErrorList[i].Cells[7].Value.ToString());
                cmd.Parameters.AddWithValue("genre", bookErrorList[i].Cells[8].Value.ToString());
                cmd.Parameters.AddWithValue("weigh", bookErrorList[i].Cells[9].Value.ToString());
                cmd.Parameters.AddWithValue("author", bookErrorList[i].Cells[10].Value.ToString());
                if (i < err.Count)
                {
                    cmd.Parameters.AddWithValue("err", err[i].ToString());
                }
                else
                {
                    cmd.Parameters.AddWithValue("err", "unknow");
                }
                cmd.ExecuteNonQuery();
            }
            connection.Close();
        }

        public void ImportToDatabase()
        {
            progressBar1.Maximum = dgvBook.RowCount-1;
            progressBar1.Minimum = 0;
            int status = 0;
            Book book = new Book();
            lblStatus.Text = status.ToString() + "%";
            for (int i = 0; i < dgvBook.RowCount; i++)
            {
                book = GetBookInfo(i);
                if (book != null)
                {
                    if (bookDAL.CreateBook(book))
                    {
                        (new Project_BookDAL()).CreateProject_Book(new Project_Book(new Project(1), (new BookDAL()).GetBookByISBN(book.ISBN), 1, 1, 1, false));
                        if (dgvBook[10, i].Value.ToString() != "")
                        {
                            Author a = (new AuthorDAL()).GetAuthorByAuthorName(dgvBook[10, i].Value.ToString());
                            if (a != null)
                            {
                                (new Book_AuthorDAL()).CreateBook_Author(new Book_Author(a, (new BookDAL()).GetBookByISBN(book.ISBN)));
                            }
                            else
                            {
                                if ((new AuthorDAL()).CreateAuthor(new Author(0, dgvBook[10, i].Value.ToString())))
                                {
                                    (new Book_AuthorDAL()).CreateBook_Author(new Book_Author((new AuthorDAL()).GetAuthorByAuthorName(dgvBook[10, i].Value.ToString()), (new BookDAL()).GetBookByISBN(book.ISBN)));
                                }
                            }
                        }
                    }
                }
                else
                {
                    bookErrorList.Add(dgvBook.Rows[i]);
                }
                progressBar1.Value = i;
                if (progressBar1.Value * 100 / progressBar1.Maximum > status)
                {
                    status = (progressBar1.Value * 100 / progressBar1.Maximum);
                    lblStatus.Text = status.ToString() + "%";
                    lblStatus.Refresh();
                }
            }
            if (bookErrorList.Count > 0)
            {
                ExportErrorBookList();
                MessageBox.Show("Đã nhập thành công " + (dgvBook.RowCount - bookErrorList.Count).ToString()  + "/" + dgvBook.RowCount + ". Phát hiện " + bookErrorList.Count + " quyển bị lỗi thông tin.", "Hoàn thành");
                Process.Start(openFileDialog1.FileName.Insert(openFileDialog1.FileName.LastIndexOf("."), "-null"));
            }
            else
            {
                MessageBox.Show("Đã nhập thành công " + (dgvBook.RowCount - bookErrorList.Count).ToString() + "/" + dgvBook.RowCount + ".");
            }
            bookErrorList.Clear();
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            btnStart.Refresh();
            btnStart.Enabled = false;
            rbtManualImport.Enabled = false;
            ImportToDatabase();
            btnStart.Enabled = false;
            rbtManualImport.Enabled = false;
        }

        private void txtISBN_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
            {
                e.Handled = true;
            }
        }

        public void LoadAuthor()
        {
            AuthorDAL aDAL = new AuthorDAL();
            List<Author> listAuthor;
            listAuthor = aDAL.GetAllAuthor();
            if (listAuthor != null)
            {
                foreach (Author au in listAuthor)
                {
                    cboAuthor.Items.Add(au.Name);
                }
            }
        }

        public void LoadGenre()
        {
            BookDAL bDAL = new BookDAL();
            List<string> listGenre;
            listGenre = bDAL.GetAllGenre();
            if (listGenre != null)
            {
                foreach (string s in listGenre)
                {
                    cboGenre.Items.Add(s);
                }
            }
        }

        public void LoadIssuer()
        {
            BookDAL bDAL = new BookDAL();
            List<string> listIssuer;
            listIssuer = bDAL.GetAllIssuer();
            if (listIssuer != null)
            {
                foreach (string s in listIssuer)
                {
                    cboIssuer.Items.Add(s);
                }
            }
        }

        public void LoadPublisher()
        {
            BookDAL bDAL = new BookDAL();
            List<string> listPublisher;
            listPublisher = bDAL.GetAllPublisher();
            if (listPublisher != null)
            {
                foreach (string s in listPublisher)
                {
                    cboPublisher.Items.Add(s);
                }
            }
        }

        private void txtISBN_Leave(object sender, EventArgs e)
        {
            if ((new Validate().ISBN(txtISBN.Text)))
            {
                if ((new BookDAL()).GetBookByISBN(Convert.ToInt64(txtISBN.Text)) != null)
                {
                    highlighter1.SetHighlightColor(txtISBN, DevComponents.DotNetBar.Validator.eHighlightColor.Red);
                    lblValidate.Text = "Mã sách này đã tồn tại.";
                }
            }
            else
            {
                highlighter1.SetHighlightColor(txtISBN, DevComponents.DotNetBar.Validator.eHighlightColor.Red);
                lblValidate.Text = "Mã sách không hợp lệ.";
            }
        }

        private void txtISBN_TextChanged(object sender, EventArgs e)
        {
            if (txtISBN.Text.Length < 10)
            {
                highlighter1.SetHighlightColor(txtISBN, DevComponents.DotNetBar.Validator.eHighlightColor.Red);
            }
            else
            {
                highlighter1.SetHighlightColor(txtISBN, DevComponents.DotNetBar.Validator.eHighlightColor.None);
            }
        }

        private void txtPrice_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsControl(e.KeyChar) && !Char.IsDigit(e.KeyChar))
            {
                e.Handled = true;
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            txtISBN_Leave(null, null);
            if (txtISBN.Text == "" || highlighter1.GetHighlightColor(txtISBN) == DevComponents.DotNetBar.Validator.eHighlightColor.Red)
            {
                txtISBN.Focus();
            }
            else
            {
                if (txtTitle.Text == "")
                {
                    highlighter1.SetHighlightColor(txtTitle, DevComponents.DotNetBar.Validator.eHighlightColor.Red);
                    lblValidate.Text = "Tên sách không được bỏ trống.";
                    txtTitle.Focus();
                }
                else
                {
                    if (txtType.Text == "")
                    {

                        highlighter1.SetHighlightColor(txtType, DevComponents.DotNetBar.Validator.eHighlightColor.Red);
                        lblValidate.Text = "Đơn vị tính không được bỏ trống.";
                        txtType.Focus();
                    }
                    else
                    {
                        if (cboPublisher.Text == "")
                        {
                            highlighter1.SetHighlightColor(cboPublisher, DevComponents.DotNetBar.Validator.eHighlightColor.Red);
                            lblValidate.Text = "Mã sách không hợp lệ.";
                            cboPublisher.Focus();
                        }
                        else
                        {
                            if (cboIssuer.Text == "")
                            {
                                highlighter1.SetHighlightColor(cboIssuer, DevComponents.DotNetBar.Validator.eHighlightColor.Red);
                                lblValidate.Text = "Mã sách không hợp lệ.";
                                cboIssuer.Focus();
                            }
                            else
                            {
                                if (txtPrice.Text == "")
                                {
                                    highlighter1.SetHighlightColor(txtPrice, DevComponents.DotNetBar.Validator.eHighlightColor.Red);
                                    lblValidate.Text = "Mã sách không hợp lệ.";
                                    txtPrice.Focus();
                                }
                                else
                                {
                                    if (txtWeigh.Text == "") txtWeigh.Text = "0";
                                    if (bookDAL.CreateBook(new Book(0, Convert.ToInt64(txtISBN.Text), txtTitle.Text, txtType.Text, cboPublisher.Text, cboIssuer.Text, txtSize.Text, txtPage.Text, Convert.ToDouble(txtPrice.Text), cboGenre.Text, Convert.ToDouble(txtWeigh.Text))))
                                    {
                                        if (cboAuthor.Text != "")
                                        {
                                            Author a = (new AuthorDAL()).GetAuthorByAuthorName(cboAuthor.Text);
                                            if (a != null)
                                            {
                                                (new Book_AuthorDAL()).CreateBook_Author(new Book_Author(a, (new BookDAL()).GetBookByISBN(Convert.ToInt64(txtISBN.Text))));
                                            }
                                            else
                                            {
                                                (new AuthorDAL()).CreateAuthor(new Author(0, cboAuthor.Text));
                                                (new Book_AuthorDAL()).CreateBook_Author(new Book_Author((new AuthorDAL()).GetAuthorByAuthorName(cboAuthor.Text), (new BookDAL()).GetBookByISBN(Convert.ToInt64(txtISBN.Text))));
                                            }
                                        }
                                        ClearAll();
                                        MessageBox.Show("Nhập sách thành công");
                                        txtISBN.Focus();
                                    }
                                    else
                                    {
                                        MessageBox.Show("Chưa nhập sách thành công");
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        public void ClearAll()
        {
            txtISBN.Text = "";
            txtPage.Text = "";
            txtPrice.Text = "";
            txtSize.Text = "";
            txtTitle.Text = "";
            txtType.Text = "";
            txtWeigh.Text = "";
            cboAuthor.Text = "";
            cboGenre.Text = "";
            cboIssuer.Text = "";
            cboPublisher.Text = "";
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có muốn làm lại từ đầu không", "Cảnh báo", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
            {
                ClearAll();
                txtISBN.Focus();
            }
        }

    }
}
